​
prices.day

​
Overview

The prices.day table provides daily token price data across all supported blockchains. It contains volume-weighted average prices for each day, ensuring a continuous time series for price analysis.

​
Table Schema

ColumnTypeDescription
blockchainvarcharBlockchain identifier (e.g., ‘ethereum’, ‘arbitrum’)
contract_addressvarbinaryToken contract address (fixed address for native tokens)
symbolvarcharToken symbol (e.g., ‘ETH’, ‘USDC’)
timestamptimestampDate timestamp (00:00 UTC of each day)
pricedoubleToken price in USD (volume-weighted average)
decimalsintToken decimals
volumedoubleTrading volume in USD (from price source)
sourcevarcharData source (‘coinpaprika’ or ‘dex.trades’)

​
Implementation Details

The daily prices are built through these steps:
  1. Collect sparse price observations from different sources
  2. Group by day, calculating volume-weighted average price per day
  3. Fill missing days with the previous day’s price (forward filling)
  4. Set a 30-day expiration for forward filling to avoid stale data

​
Usage

This table is ideal for analyzing daily price trends and performing day-over-day comparisons. It provides a good balance between data granularity and query performance for longer-term analyses. Note: The underlying data sources are aligned to 5-minute intervals for consistency and improved data quality.

​
Latency and Update Frequency

The prices.day table is updated daily at 00:00 UTC, providing the closing price for the previous day. Data is typically available within 30 minutes of the daily close.

​
Usage Examples

​
Get ETH price history for the last month:

SELECT
  timestamp,
  price
FROM prices.day
WHERE blockchain = 'ethereum'
AND contract_address = 0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2 -- WETH
AND timestamp >= NOW() - INTERVAL '30' DAY
ORDER BY timestamp

​
Calculate monthly average price for multiple tokens:

SELECT
  blockchain,
  symbol, -- Symbol included for readable output only
  date_trunc('month', timestamp) as month,
  avg(price) as avg_price
FROM prices.day
WHERE blockchain = 'ethereum'
AND contract_address IN (
  0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2, -- WETH
  0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48, -- USDC
  0x2260fac5e5542a773aa44fbcfedf7c193bc2c599  -- WBTC
)
AND timestamp >= NOW() - INTERVAL '90' DAY
GROUP BY 1,2,3
ORDER BY 1,2,3

​
Get native token price using dune.blockchains:

-- First get the native token address
WITH native_token AS (
  SELECT token_address
  FROM dune.blockchains
  WHERE name = 'ethereum'
)
-- Then use it to query the price
SELECT
  timestamp,
  price
FROM prices.day, native_token
WHERE blockchain = 'ethereum'
AND contract_address = token_address
AND timestamp >= NOW() - INTERVAL '30' DAY
ORDER BY timestamp

​
Data Quality Notes

  • Prices older than 30 days will not be forward-filled to avoid using stale data
  • Native tokens (like ETH, BNB) are assigned fixed addresses for consistency
  • For native tokens, use dune.blockchains table to get the standardized address for each blockchain
  • The same token may have different prices on different blockchains due to bridging inefficiencies
  • Always use contract_address and blockchain for precise token identification, never use symbol for joins or filters
  • Prices are calculated using volume-weighted averages for more accurate representation
  • Quality Filtering: The system includes filtering mechanisms to reduce the impact of anomalous trades
  • Data Limitations: While filtering helps improve data quality, users should always validate prices for critical applications